package com.ztc.ZtcSpecialRecommendation.dao;

import com.aqgj.util.BaseDaoImpl;
import com.aqgj.util.Pager;
import com.ztc.ZtcSpecialRecommendation.bean.ZtcSpecialRecommendation;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;


@Repository("ZtcSpecialRecommendationDao")
public class ZtcSpecialRecommendationDao extends BaseDaoImpl<ZtcSpecialRecommendation> {

    public String save(ZtcSpecialRecommendation ztcSpecialRecommendation){
        this.getHibernateTemplate().save(ztcSpecialRecommendation);
        return ztcSpecialRecommendation.getId();
    }

    //搜索线路站点信息（新增特价推荐）
    public List<Map> searchClassForSaleBySite(String startSite, String endSite) {
        String sql= "select r.id,s1.area_city startArea,s2.area_city endArea,s1.id startId,s2.id endId,z.full_name fullName,p.price,z.id cid,r.name from ztc_route r " +
                    "inner join ztc_transport z on z.id=r.fk_ztc_transport_id " +
                    "inner join ztc_route_price p on p.fk_ztc_route_id =r.id  " +
                    "inner join ztc_route_site s1 on s1.id =p.fk_route_start_site_id and s1.area_city like '%"+startSite+"%' " +
                    "inner join ztc_route_site s2 on s2.id =p.fk_route_end_site_id and s2.area_city like '%"+endSite+"%' " +
                    "inner join ztc_classes zc on zc.fk_ztc_route_id=r.id " +
                    "inner join ztc_classes_for_sale c on c.fk_ztc_classes_id=zc.id " +
                    "where c.ticket_status=0 and c.`type`=0 and r.`status`=1 group by r.id,s1.id,s2.id ";
        return this.currentSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
    }

    //获取所有正常推荐
    public List<Map> getSpecialALLList(Integer type) {
        String sql= "SELECT s3.area_city startArea,s4.area_city endArea,p1.seat_info,s.`type`,s.id,c.departure_date departureDate,z.id cid,z.full_name fullName " +
                    "FROM ztc_special_recommendation s " +
                    "INNER JOIN ztc_route_site s1 ON s1.id=s.fk_route_start_site_id " +
                    "INNER JOIN ztc_route_site s2 ON s2.id=s.fk_route_end_site_id " +
                    "INNER JOIN ztc_classes zc ON zc.fk_ztc_route_id=s.fk_ztc_route_id " +
                    "INNER JOIN ztc_transport z ON z.id=zc.fk_transport_id " +
                    "INNER JOIN ztc_classes_for_sale c ON zc.id=c.fk_ztc_classes_id " +
                    "INNER JOIN " +
                    "(SELECT MIN(w.departure_date) departure_date,w.fk_ztc_classes_id " +
                    "FROM ztc_classes_for_sale w where w.departure_date>=curdate() and w.ticket_status=0 " +
                    "GROUP BY w.fk_ztc_classes_id) x ON " +
                    "x.fk_ztc_classes_id=zc.id AND c.departure_date=x.departure_date " +
                    "INNER JOIN ztc_classes_price_for_sale p1 ON p1.fk_classes_for_sale_id=c.id " +
                    "INNER JOIN ztc_classes_site_for_sale s3 ON s3.id=p1.fk_start_classes_site_for_sale_id AND s3.area_city=s1.area_city " +
                    "INNER JOIN ztc_classes_site_for_sale s4 ON s4.id=p1.fk_end_classes_site_for_sale_id AND s4.area_city=s2.area_city " +
                    "WHERE zc.ticket_status=0 AND c.ticket_status=0 AND c.`type`=0 AND s.`status`=1 AND s.type="+type+" " +
                    "ORDER BY s.create_time DESC ";
        return this.currentSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
    }


    //获取是否已存在推荐
    public List<ZtcSpecialRecommendation> getZtcSpecialListByRoutId(String routeId,String startId,String endId,Integer type) {
        String sql = "SELECT  * from  ztc_special_recommendation where fk_route_start_site_id=? and fk_route_end_site_id=? and fk_ztc_route_id=? and type=? ";
        return this.currentSession().createNativeQuery(sql, ZtcSpecialRecommendation.class).setParameter(1,startId).setParameter(2,endId).setParameter(3,routeId).setParameter(4,type).getResultList();
    }
}
